# -*- coding: utf-8 -*-
from selenium import webdriver  # 获取天气状况（7天）
from lxml import etree
import pymysql
import time


def write_log(date, weather, c1, c2, w_wind):
    with open('log.txt', 'a+', encoding='utf-8') as f:
        f.write(date + ' ' + weather + ' ' + c1 + ' ' + c2 + ' ' + w_wind)
        f.write("\n")


def creat_db():
    # 打开数据库连接
    db = pymysql.connect("localhost", "root", "5201314", "weather")
    # 使用 cursor() 方法创建一个游标对象 cursor
    cursor = db.cursor()
    # 使用预处理语句创建表
    sql = """CREATE TABLE week (
             w_id int(255) NOT NULL AUTO_INCREMENT,
             w_date varchar(25),
             w_weather varchar(25),
             w_c1 varchar(25),
             w_c2 varchar(25),
             w_wind varchar(25),
             PRIMARY KEY ( `w_id` )
             )"""
    try:
        # 执行sql语句
        cursor.execute(sql)
        # 提交到数据库执行
        db.commit()
    except:
        # 如果发生错误则回滚
        db.rollback()
    # 关闭数据库连接
    db.close()


def save_db(w_date, w_weather, w_c1, w_c2, w_wind):
    # 打开数据库连接
    db = pymysql.connect("localhost", "root", "5201314", "weather")
    # 使用 cursor() 方法创建一个游标对象 cursor
    cursor = db.cursor()
    sql = "INSERT INTO week(w_date, w_weather, w_c1, w_c2, w_wind) VALUES ('{}', '{}','{}', '{}', '{}') ".format(w_date,
                                                                                                                 w_weather,
                                                                                                                 w_c1,
                                                                                                                 w_c2,
                                                                                                                 w_wind)
    try:
        # 执行sql语句
        cursor.execute(sql)
        # 提交到数据库执行
        db.commit()
    except:
        # 如果发生错误则回滚
        db.rollback()
    # 关闭数据库连接
    db.close()


def update_db(w_date, w_weather, w_c1, w_c2, w_wind, id):
    # 打开数据库连接
    db = pymysql.connect("localhost", "root", "5201314", "weather")
    # 使用 cursor() 方法创建一个游标对象 cursor
    cursor = db.cursor()
    sql = "UPDATE week SET w_date='{}', w_weather='{}', w_c1='{}', w_c2='{}', w_wind='{}' WHERE w_id={}; ".format(
        w_date,
        w_weather,
        w_c1,
        w_c2,
        w_wind,
        id)
    try:
        # 执行sql语句
        cursor.execute(sql)
        # 提交到数据库执行
        db.commit()
    except:
        # 如果发生错误则回滚
        db.rollback()
        # 关闭数据库连接
        db.close()


def one_week(text, a):
    news = text.xpath('//*[@id="7d"]/ul/li')  # 获取含有七天天气状况的列表
    id = 0
    for t in news:
        id += 1
        date = t.xpath('h1/text()')[0]  # 日期
        weather = t.xpath('p[1]/text()')[0]  # 天气
        c1 = t.xpath('p[2]/span/text()')  # 最高温度
        c2 = t.xpath('p[2]/i/text()')[0]  # 最低温度
        wind = t.xpath('p[3]/em/span/@title')  # 风向，风力
        if (c1 == []):
            c1 = " "
        else:
            c1 = c1[0]
        w_wind = ''
        for i in range(len(wind)):
            w_wind += wind[i] + ','
        print(date, weather, c1, c2, w_wind)
        write_log(date, weather, c1, c2, w_wind)
        if (a == 1):
            save_db(date, weather, c1, c2, w_wind)
        else:
            update_db(date, weather, c1, c2, w_wind, id)

    #   ['26日（今天）'] ['多云'] [] ['18℃'] ['西风']
    #   ['27日（明天）'] ['多云转晴'] ['28℃'] ['18℃'] ['东风', '无持续风向']


if __name__ == '__main__':
    creat_db()
    driver = webdriver.Firefox()
    driver.get("http://www.weather.com.cn/weather/101210101.shtml")
    html = driver.page_source
    driver.quit()
    text = etree.HTML(html)
    a = 0
    while (True):
        a += 1
        one_week(text, a)
        time.sleep(28800)  # 8 hours
